package net.comm.user.db;

import org.sakaiproject.db.api.iServiceSql;


/**
 * methods for accessing user data in a database.
 */
public class UsertempServiceSqlMysql implements iServiceSql
{
	/**
	 * 返回根据主键删除语句
	 */
	public String getDeleteSql()
	{
		return "delete from Usertemp where UserID=?";
	}
	/**
	 * 返回插入语句
	 */
	public String getInsertSql()
	{
		return "insert into Usertemp ( UserID,Nick,Name,Introduction,PicID,Sex,Work,WorkSet," +
				"Skill,SkillSet,Birthday,BirthdaySet,Star,Chzodiac,Nationality,Pepole,Polity," +
				"JoinWorkTime,Home_location,Home_sublocation,Home_area,Home_town,Home_Set,Certtype," +
				"Certno,CertSet,Marriage,IsArmy,Army,ArmySet,Education,HighSchool,HsFinshDate,College," +
				"ColFinshDate,SchoolSet,Interest,InterestSet,Work_location,Work_sublocation," +
				"Work_area,VillageID,Building,HouseNo,FangchanNo,FcNoSet,FcPicID,FcPicIDSts," +
				"InTime,InTimeSet,FamSts,FamStsSet,LiveSts,LiveStsSet,Offwebsite,Perwebsite," +
				"Blog,Mobile,Tel,Famtel,Fax,QQ,Msn,Email,Addr,Postcode,Mtime,SchoolSet2 " +
				") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?" +
				",?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )";
	}
	
	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdateSql()
	{
		return "update Usertemp  set Nick=?, Name=?, Introduction=?, PicID=?, Sex=?, Work=?, WorkSet=?, " +
				"Skill=?, SkillSet=?, Birthday=?, BirthdaySet=?, Star=?, Chzodiac=?, Nationality=?, Pepole=?, Polity=?, " +
				"JoinWorkTime=?, Home_location=?, Home_sublocation=?, Home_area=?, Home_town=?, Home_Set=?, Certtype=?, " +
				"Certno=?, CertSet=?, Marriage=?, IsArmy=?, Army=?, ArmySet=?, Education=?, HighSchool=?, HsFinshDate=?, College=?, " +
				"ColFinshDate=?, SchoolSet=?, Interest=?, InterestSet=?, Work_location=?, Work_sublocation=?, " +
				"Work_area=?, VillageID=?, Building=?, HouseNo=?, FangchanNo=?, FcNoSet=?, FcPicID=?, FcPicIDSts=?, " +
				"InTime=?, InTimeSet=?, FamSts=?, FamStsSet=?, LiveSts=?, LiveStsSet=?, Offwebsite=?, Perwebsite=?, " +
				"Blog=?, Mobile=?, Tel=?, Famtel=?, Fax=?, QQ=?, Msn=?, Email=?, Addr=?, Postcode=?, Mtime=? " +
				"  where UserID=? ";
	}

	/**
	 * 返回根据主键查询语句
	 */
	public String getbykeySql()
	{
		return "select  UserID,Nick,Name,Introduction,PicID,Sex,Work,WorkSet," +
				"Skill,SkillSet,Birthday,BirthdaySet,Star,Chzodiac,Nationality,Pepole,Polity," +
				"JoinWorkTime,Home_location,Home_sublocation,Home_area,Home_town,Home_Set,Certtype," +
				"Certno,CertSet,Marriage,IsArmy,Army,ArmySet,Education,HighSchool,HsFinshDate,College," +
				"ColFinshDate,SchoolSet,Interest,InterestSet,Work_location,Work_sublocation," +
				"Work_area,VillageID,Building,HouseNo,FangchanNo,FcNoSet,FcPicID,FcPicIDSts," +
				"InTime,InTimeSet,FamSts,FamStsSet,LiveSts,LiveStsSet,Offwebsite,Perwebsite," +
				"Blog,Mobile,Tel,Famtel,Fax,QQ,Msn,Email,Addr,Postcode,Mtime,SchoolSet2 " +
				" from Usertemp where UserID=? ";
	}
	
	/**
	 * 返回根据会员ID查询语句 
	 */
	public String getByUserIDSql()
	{
		StringBuffer sb =  new StringBuffer("select UserID,Nick,Name,Introduction,PicID,Sex,Work,WorkSet," +
				"Skill,SkillSet,Birthday,BirthdaySet,Star,Chzodiac,Nationality,Pepole,Polity," +
				"JoinWorkTime,Home_location,Home_sublocation,Home_area,Home_town,Home_Set,Certtype," +
				"Certno,CertSet,Marriage,IsArmy,Army,ArmySet,Education,HighSchool,HsFinshDate,College," +
				"ColFinshDate,SchoolSet,SchoolSet2,Interest,InterestSet,Work_location,Work_sublocation," +
				"Work_area,VillageID,Building,HouseNo,FangchanNo,FcNoSet,FcPicID,FcPicIDSts," +
				"InTime,InTimeSet,FamSts,FamStsSet,LiveSts,LiveStsSet,Offwebsite,Perwebsite," +
				"Blog,Mobile,Tel,Famtel,Fax,QQ,Msn,Email,Addr,Postcode,Mtime " +
				" from Usertemp ");
		
		sb.append(" where UserID=?  Order by MTime desc ");
				
		return sb.toString();
	}
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select UserID,Nick,Name,Introduction,PicID,Sex,Work,WorkSet," +
				"Skill,SkillSet,Birthday,BirthdaySet,Star,Chzodiac,Nationality,Pepole,Polity," +
				"JoinWorkTime,Home_location,Home_sublocation,Home_area,Home_town,Home_Set,Certtype," +
				"Certno,CertSet,Marriage,IsArmy,Army,ArmySet,Education,HighSchool,HsFinshDate,College," +
				"ColFinshDate,SchoolSet,SchoolSet2,Interest,InterestSet,Work_location,Work_sublocation," +
				"Work_area,VillageID,Building,HouseNo,FangchanNo,FcNoSet,FcPicID,FcPicIDSts," +
				"InTime,InTimeSet,FamSts,FamStsSet,LiveSts,LiveStsSet,Offwebsite,Perwebsite," +
				"Blog,Mobile,Tel,Famtel,Fax,QQ,Msn,Email,Addr,Postcode,Mtime " +
				" from Usertemp ");
		
		sb.append(" where 1=1 ");
		sb.append(" limit ?, ?");
				
		return sb.toString();	
	}	
	
	/**
	 * 返回所有记录数
	 */
	public String getCountSql1()
	{
		return "select count(*) as COUNT from Usertemp";
	}


	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql2()
	{
		return "select count(*) as COUNT from Usertemp where UserID=? ";
	}

	/**
	 * 返回根据主键修改个人照片语句
	 */
	public String getUpdPicSql()
	{
		return "update Usertemp  set Pic=?  where UserID=? ";
	}
	
	
	/**
	 * @see net.comm.job.db.iServiceSql.impl.UserServiceSql#getMaxInputsForSelectWhereInQueries()
	 */
	public int getMaxInputsForSelectWhereInQueries() {
		// For Oracle, the maximum supported number of expressions in a list is 1000. 
		return 1000;
	}
}
